package org.hit.burkun.mapping;

import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.Map;
import java.util.Set;
//import java.util.Map.Entry;

import org.hit.burkun.db.DBHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class BasicOfflineMapping implements IOfflineMappingProcessor {
	private static Map<DBKEY, String> FIELDS = new HashMap<IOfflineMappingProcessor.DBKEY, String>();
	static {
		FIELDS.put(DBKEY.EMBL_GenBank_DDBJ, "ensembl_gene_id");
		FIELDS.put(DBKEY.GENE_SYMBOL, "symbol");
		FIELDS.put(DBKEY.GENE_SYMBOL_SYN, "alias_symbols");
		FIELDS.put(DBKEY.GeneID, "entrez_id");
		FIELDS.put(DBKEY.MIM, "omim_ids");
		FIELDS.put(DBKEY.RefSeq, "refseq_accession");
		FIELDS.put(DBKEY.HGNC, "hgnc_id");
		FIELDS.put(DBKEY.UniProtKB_AC, "uniport_ids");

	}
	private static Logger logger = LoggerFactory
			.getLogger(BasicOfflineMapping.class);

	public BasicOfflineMapping(DBHelper dbHelper) {
		this.dbh = dbHelper;
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		DBHelper dbh = DBHelper.getLocalBioSearch();
		BasicOfflineMapping mapper = new BasicOfflineMapping(dbh);
		Collection<String> ids = new LinkedList<String>();
		ids.add("A1BG");
		ids.add("A1BG-AS1");
		mapper.begin();
		mapper.mapping(DBKEY.GENE_SYMBOL, DBKEY.HGNC, ids);
		mapper.end();
	}

	private DBHelper dbh;
	private int maxBatchNum = 100;
	private int unmappedCounter = 0;

	@Override
	public void process(DBKEY fromKey, DBKEY toKey,
			Map<String, Set<String>> data) {
		/*
		 * Set<Entry<String, Set<String>>> enties = data.entrySet(); for
		 * (Entry<String, Set<String>> entry : enties) { String key =
		 * entry.getKey(); Set<String> values = entry.getValue();
		 * System.out.println(key); for (String v : values) {
		 * System.out.println(v); } }
		 */
	}

	@Override
	public void mapping(DBKEY fromKey, DBKEY toKey, Collection<String> ids) {
		if (fromKey != toKey) {
			unmappedCounter = ids.size();
			String sqlStr = assembleQueryStr(fromKey, toKey);
			logger.debug("get sqlquery template " + sqlStr);
			int counter = 0;
			if (fromKey == DBKEY.GENE_SYMBOL || fromKey == DBKEY.MIM
					|| fromKey == DBKEY.UniProtKB_AC) {
				// gene symbol要单独处理, 因为有find_in_set
				Collection<String[]> res = new LinkedList<String[]>();
				for (String id : ids) {
					if (counter == maxBatchNum) {
						counter = 0;
						Map<String, Set<String>> mapres = getMapedData(res);
						process(fromKey, toKey, mapres);
						res.clear();
					}
					counter += 1;
					String queryStr = String.format(sqlStr, id, id, id);
					Collection<String[]> back = dbh.queryData(queryStr, 2);
					res.addAll(back);
				}
				// 还有部分要继续处理吆~
				if (counter > 0) {
					Map<String, Set<String>> mapres = getMapedData(res);
					process(fromKey, toKey, mapres);
					res.clear();
				}

			} else {
				StringBuilder sb = new StringBuilder();
				for (String id : ids) {
					if (counter == maxBatchNum) {
						sb.deleteCharAt(sb.length() - 1);
						String dataStr = sb.toString();
						String queryStr = String.format(sqlStr, dataStr,
								dataStr);
						Collection<String[]> res = dbh.queryData(queryStr, 2);
						Map<String, Set<String>> mapres = getMapedData(res);
						process(fromKey, toKey, mapres);
						sb.setLength(0);
						sb.append("'" + id + "'").append(",");
						counter = 0;
					} else {
						sb.append("'" + id + "',");
					}
					counter += 1;
				}
				// 还有部分要继续处理吆~
				if (counter > 0) {
					sb.deleteCharAt(sb.length() - 1);
					String dataStr = sb.toString();
					String queryStr = String.format(sqlStr, dataStr, dataStr);
					Collection<String[]> res = dbh.queryData(queryStr, 2);
					Map<String, Set<String>> mapres = getMapedData(res);
					process(fromKey, toKey, mapres);
					sb.setLength(0);
				}
			}
		}

	}

	// 只能一个一个查，效率很低很低
	private static String querySymbolToOtherTempl = "SELECT %s, symbol FROM hgnc_complete_set where (symbol = '%s' or find_in_set('%s', alias_symbols)) and status = 'Approved'";
	// SELECT hgnc_id, '138670' as omim_id FROM hgnc_complete_set where (omim_id
	// = '138670' or find_in_set('138670', omim_id)) and status = 'Approved'
	private static String queryFindinSetProToOtherTempl = "SELECT %s, %s FROM hgnc_complete_set where (%s = %s or find_in_set(%s, %s)) and status = 'Approved'";
	private static String queryOtherToOtherTempl = "SELECT %s,%s FROM hgnc_complete_set where %s in (%s) and status = 'Approved'";

	private String assembleQueryStr(DBKEY fromKey, DBKEY toKey) {
		if (fromKey == DBKEY.GENE_SYMBOL) {
			return String.format(querySymbolToOtherTempl, FIELDS.get(toKey),
					"%s", "%s");
		} else if (fromKey == DBKEY.MIM || fromKey == DBKEY.UniProtKB_AC) {
			String strSql = String.format(queryFindinSetProToOtherTempl,
					FIELDS.get(toKey), "'%s'", FIELDS.get(fromKey), "'%s'",
					"'%s'", FIELDS.get(fromKey));
			return strSql;
		}
		return String.format(queryOtherToOtherTempl, FIELDS.get(toKey),
				FIELDS.get(fromKey), FIELDS.get(fromKey), "%s");
	}

	private Map<String, Set<String>> getMapedData(Collection<String[]> data) {
		logger.debug("convert data to Map<String, Set<String>>...");
		Map<String, Set<String>> table = new HashMap<String, Set<String>>();
		for (String[] arg : data) {
			String value = arg[0];
			String key = arg[1];
			// value may contain comma, so first split
			if (value != null && key != null) {
				String[] vs = value.split(",");

				if (table.containsKey(key)) {
					for (String v : vs) {
						table.get(key).add(v);
					}
				} else {
					HashSet<String> set = new HashSet<>();
					for (String v : vs) {
						set.add(v);
					}
					table.put(key, set);
				}
			}
		}
		unmappedCounter -= table.size();
		return table;
	}

	@Override
	public void begin() {
		logger.debug("init connection...");
		dbh.connectionDB();
	}

	@Override
	public void end() {
		if (unmappedCounter > 0) {
			logger.warn("there have " + unmappedCounter + " can not map!");
		}
		logger.debug("clean connection...");
		dbh.disconnection();
	}

}
